package com.example.medicalapp.helper;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.example.medicalapp.model.Appointment;
import com.example.medicalapp.model.Department;
import com.example.medicalapp.model.User;
import com.example.medicalapp.model.Prescription;

import java.util.ArrayList;
import java.util.List;

public class DatabaseHelper extends SQLiteOpenHelper {
    // 数据库版本和名称
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "MedicalAssistantDB";
    private static final String TAG = "DatabaseHelper";

    // 表名
    private static final String TABLE_USERS = "Users";
    private static final String TABLE_DEPARTMENTS = "Departments";
    private static final String TABLE_APPOINTMENTS = "Appointments";
    private static final String TABLE_PRESCRIPTIONS = "Prescriptions";

    // Users表列名
    private static final String KEY_ID = "id";
    private static final String KEY_USERNAME = "username";
    private static final String KEY_PASSWORD = "password";
    private static final String KEY_EMAIL = "email";
    private static final String KEY_ROLE = "role";
    private static final String KEY_DOCTOR_ID = "doctor_id";

    // Departments表列名
    private static final String KEY_DEPT_NAME = "name";

    // Appointments表列名
    private static final String KEY_PATIENT_ID = "patient_id";
    private static final String KEY_DEPT_ID = "dept_id";
    private static final String KEY_PATIENT_NAME = "patient_name";
    private static final String KEY_ID_CARD = "id_card";
    private static final String KEY_APPOINT_TIME = "appoint_time";
    private static final String KEY_STATUS = "status";

    // Prescriptions表列名
    private static final String KEY_APPOINTMENT_ID = "appointment_id";
    private static final String KEY_DOCTOR_ID_PRESCRIPTION = "doctor_id";
    private static final String KEY_AI_ANALYSIS = "ai_analysis";
    private static final String KEY_FINAL_DIAGNOSIS = "final_diagnosis";
    private static final String KEY_FINAL_PRESCRIPTION = "final_prescription";
    private static final String KEY_CREATED_DATE = "created_date";

    // 创建表的SQL语句
    private static final String CREATE_TABLE_USERS = "CREATE TABLE " + TABLE_USERS + "("
            + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_USERNAME + " TEXT UNIQUE,"
            + KEY_PASSWORD + " TEXT,"
            + KEY_EMAIL + " TEXT,"
            + KEY_ROLE + " TEXT,"
            + KEY_DOCTOR_ID + " TEXT" + ")";

    private static final String CREATE_TABLE_DEPARTMENTS = "CREATE TABLE " + TABLE_DEPARTMENTS + "("
            + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_DEPT_NAME + " TEXT" + ")";

    private static final String CREATE_TABLE_APPOINTMENTS = "CREATE TABLE " + TABLE_APPOINTMENTS + "("
            + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_PATIENT_ID + " INTEGER,"
            + KEY_DEPT_ID + " INTEGER,"
            + KEY_PATIENT_NAME + " TEXT,"
            + KEY_ID_CARD + " TEXT,"
            + KEY_APPOINT_TIME + " TEXT,"
            + KEY_STATUS + " TEXT DEFAULT 'pending'" + ")";

    private static final String CREATE_TABLE_PRESCRIPTIONS = "CREATE TABLE " + TABLE_PRESCRIPTIONS + "("
            + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_APPOINTMENT_ID + " INTEGER,"
            + KEY_PATIENT_ID + " INTEGER,"
            + KEY_DOCTOR_ID_PRESCRIPTION + " INTEGER,"
            + KEY_AI_ANALYSIS + " TEXT,"
            + KEY_FINAL_DIAGNOSIS + " TEXT,"
            + KEY_FINAL_PRESCRIPTION + " TEXT,"
            + KEY_CREATED_DATE + " TEXT" + ")";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        Log.d(TAG, "DatabaseHelper initialized");
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建所有表
        db.execSQL(CREATE_TABLE_USERS);
        db.execSQL(CREATE_TABLE_DEPARTMENTS);
        db.execSQL(CREATE_TABLE_APPOINTMENTS);
        db.execSQL(CREATE_TABLE_PRESCRIPTIONS);

        // 初始化科室数据
        initializeDepartments(db);

        Log.d(TAG, "Tables created and initialized");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 升级数据库时删除旧表
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_DEPARTMENTS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_APPOINTMENTS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRESCRIPTIONS);

        // 重新创建表
        onCreate(db);
        Log.d(TAG, "Database upgraded from version " + oldVersion + " to " + newVersion);
    }

    // 初始化科室数据
    private void initializeDepartments(SQLiteDatabase db) {
        String[] departments = {
                "内科", "外科", "儿科", "妇产科", "眼科",
                "耳鼻喉科", "口腔科", "皮肤科", "精神科", "中医科"
        };

        for (String dept : departments) {
            ContentValues values = new ContentValues();
            values.put(KEY_DEPT_NAME, dept);
            db.insert(TABLE_DEPARTMENTS, null, values);
        }
        Log.d(TAG, "Departments initialized");
    }

    // 用户相关操作
    public long addUser(User user) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(KEY_USERNAME, user.getUsername());
        values.put(KEY_PASSWORD, user.getPassword());
        values.put(KEY_EMAIL, user.getEmail());
        values.put(KEY_ROLE, user.getRole());
        values.put(KEY_DOCTOR_ID, user.getDoctorId());

        // 插入行
        long id = db.insert(TABLE_USERS, null, values);
        db.close(); // 关闭数据库连接

        Log.d(TAG, "User added with id: " + id);
        return id;
    }

    public User checkUser(String username, String password) {
        // 查询语句：SELECT * FROM users WHERE username = ? AND password = ?
        String[] columns = {KEY_ID, KEY_USERNAME, KEY_EMAIL, KEY_ROLE, KEY_DOCTOR_ID};
        SQLiteDatabase db = this.getReadableDatabase();

        // 选择条件
        String selection = KEY_USERNAME + " = ?" + " AND " + KEY_PASSWORD + " = ?";

        // 选择条件的参数
        String[] selectionArgs = {username, password};

        // 执行查询
        Cursor cursor = db.query(TABLE_USERS, columns, selection, selectionArgs,
                null, null, null);

        User user = null;
        if (cursor.moveToFirst()) {
            user = new User();
            user.setId(cursor.getInt(0));
            user.setUsername(cursor.getString(1));
            user.setEmail(cursor.getString(2));
            user.setRole(cursor.getString(3));
            user.setDoctorId(cursor.getString(4));

            Log.d(TAG, "User found: " + user.getUsername());
        } else {
            Log.d(TAG, "User not found with username: " + username);
        }

        cursor.close();
        db.close();
        return user;
    }

    // 科室相关操作
    public List<Department> getAllDepartments() {
        List<Department> departments = new ArrayList<>();
        String selectQuery = "SELECT * FROM " + TABLE_DEPARTMENTS;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // 遍历所有行并添加到列表
        if (cursor.moveToFirst()) {
            do {
                Department dept = new Department();
                dept.setId(cursor.getInt(0));
                dept.setName(cursor.getString(1));
                departments.add(dept);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        Log.d(TAG, "Fetched " + departments.size() + " departments");
        return departments;
    }

    // 预约相关操作
    public long addAppointment(Appointment appointment) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(KEY_PATIENT_ID, appointment.getPatientId());
        values.put(KEY_DEPT_ID, appointment.getDeptId());
        values.put(KEY_PATIENT_NAME, appointment.getPatientName());
        values.put(KEY_ID_CARD, appointment.getIdCard());
        values.put(KEY_APPOINT_TIME, appointment.getAppointTime());
        values.put(KEY_STATUS, appointment.getStatus());

        // 插入行
        long id = db.insert(TABLE_APPOINTMENTS, null, values);
        db.close();

        Log.d(TAG, "Appointment added with id: " + id);
        return id;
    }

    public String getLastAppointmentTime(int deptId) {
        SQLiteDatabase db = this.getReadableDatabase();
        String lastTime = null;

        // 查询指定科室最近的预约时间
        String selectQuery = "SELECT " + KEY_APPOINT_TIME + " FROM " + TABLE_APPOINTMENTS
                + " WHERE " + KEY_DEPT_ID + " = ?"
                + " ORDER BY " + KEY_APPOINT_TIME + " DESC LIMIT 1";

        Cursor cursor = db.rawQuery(selectQuery, new String[]{String.valueOf(deptId)});

        if (cursor.moveToFirst()) {
            lastTime = cursor.getString(0);
            Log.d(TAG, "Last appointment time for dept " + deptId + ": " + lastTime);
        } else {
            Log.d(TAG, "No appointments found for dept " + deptId);
        }

        cursor.close();
        db.close();
        return lastTime;
    }

    // 获取用户信息通过ID
    public User getUserById(int userId) {
        SQLiteDatabase db = this.getReadableDatabase();
        User user = null;

        String[] columns = {KEY_ID, KEY_USERNAME, KEY_EMAIL, KEY_ROLE, KEY_DOCTOR_ID};
        String selection = KEY_ID + " = ?";
        String[] selectionArgs = {String.valueOf(userId)};

        Cursor cursor = db.query(TABLE_USERS, columns, selection, selectionArgs,
                null, null, null);

        if (cursor.moveToFirst()) {
            user = new User();
            user.setId(cursor.getInt(0));
            user.setUsername(cursor.getString(1));
            user.setEmail(cursor.getString(2));
            user.setRole(cursor.getString(3));
            user.setDoctorId(cursor.getString(4));

            Log.d(TAG, "User found with id: " + userId);
        } else {
            Log.d(TAG, "User not found with id: " + userId);
        }

        cursor.close();
        db.close();
        return user;
    }

    /**
     * 获取某个科室最近的预约时间
     */
    public String getLatestAppointmentTime(int deptId) {
        SQLiteDatabase db = this.getReadableDatabase();
        String time = null;

        // 查询该科室最新的预约时间
        String selectQuery = "SELECT " + KEY_APPOINT_TIME + " FROM " + TABLE_APPOINTMENTS +
                " WHERE " + KEY_DEPT_ID + " = ?" +
                " ORDER BY " + KEY_APPOINT_TIME + " DESC LIMIT 1";

        Cursor cursor = db.rawQuery(selectQuery, new String[]{String.valueOf(deptId)});

        if (cursor.moveToFirst()) {
            time = cursor.getString(cursor.getColumnIndexOrThrow(KEY_APPOINT_TIME));
        }
        cursor.close();
        return time;
    }

    /**
     * 插入新的预约记录
     */
    public long insertAppointment(int patientId, int deptId, String patientName,
                                  String idCard, String appointTime, String status) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_PATIENT_ID, patientId);
        values.put(KEY_DEPT_ID, deptId);
        values.put(KEY_PATIENT_NAME, patientName);
        values.put(KEY_ID_CARD, idCard);
        values.put(KEY_APPOINT_TIME, appointTime);
        values.put(KEY_STATUS, status);

        // 插入行
        long id = db.insert(TABLE_APPOINTMENTS, null, values);
        db.close(); // 关闭数据库连接

        return id;
    }

    /**
     * 获取指定科室的所有待诊患者
     */
    public List<Appointment> getPendingAppointmentsByDept(int deptId) {
        List<Appointment> appointments = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        String selectQuery = "SELECT * FROM " + TABLE_APPOINTMENTS +
                " WHERE " + KEY_DEPT_ID + " = ? AND " + KEY_STATUS + " = ?" +
                " ORDER BY " + KEY_APPOINT_TIME + " ASC";

        Cursor cursor = db.rawQuery(selectQuery, new String[]{
                String.valueOf(deptId), "pending"
        });

        if (cursor.moveToFirst()) {
            do {
                Appointment appointment = new Appointment();
                appointment.setId(cursor.getInt(cursor.getColumnIndexOrThrow(KEY_ID)));
                appointment.setPatientId(cursor.getInt(cursor.getColumnIndexOrThrow(KEY_PATIENT_ID)));
                appointment.setDeptId(cursor.getInt(cursor.getColumnIndexOrThrow(KEY_DEPT_ID)));
                appointment.setPatientName(cursor.getString(cursor.getColumnIndexOrThrow(KEY_PATIENT_NAME)));
                appointment.setIdCard(cursor.getString(cursor.getColumnIndexOrThrow(KEY_ID_CARD)));
                appointment.setAppointTime(cursor.getString(cursor.getColumnIndexOrThrow(KEY_APPOINT_TIME)));
                appointment.setStatus(cursor.getString(cursor.getColumnIndexOrThrow(KEY_STATUS)));

                appointments.add(appointment);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        Log.d(TAG, "Fetched " + appointments.size() + " pending appointments for dept: " + deptId);
        return appointments;
    }

    /**
     * 更新预约状态
     */
    public int updateAppointmentStatus(int appointmentId, String status) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_STATUS, status);

        // 更新行
        int rowsAffected = db.update(TABLE_APPOINTMENTS, values,
                KEY_ID + " = ?", new String[]{String.valueOf(appointmentId)});
        db.close();

        Log.d(TAG, "Updated appointment " + appointmentId + " status to: " + status +
                ", rows affected: " + rowsAffected);
        return rowsAffected;
    }

    /**
     * 插入新的处方记录
     */
    public long insertPrescription(Prescription prescription) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_APPOINTMENT_ID, prescription.getAppointmentId());
        values.put(KEY_PATIENT_ID, prescription.getPatientId());
        values.put(KEY_DOCTOR_ID_PRESCRIPTION, prescription.getDoctorId());
        values.put(KEY_AI_ANALYSIS, prescription.getAiAnalysis());
        values.put(KEY_FINAL_DIAGNOSIS, prescription.getFinalDiagnosis());
        values.put(KEY_FINAL_PRESCRIPTION, prescription.getFinalPrescription());
        values.put(KEY_CREATED_DATE, prescription.getCreatedDate());

        // 插入行
        long id = db.insert(TABLE_PRESCRIPTIONS, null, values);
        db.close();

        Log.d(TAG, "Prescription added with id: " + id);
        return id;
    }

    /**
     * 获取患者的所有处方记录
     */
    public List<Prescription> getPrescriptionsByPatient(int patientId) {
        List<Prescription> prescriptions = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        String selectQuery = "SELECT * FROM " + TABLE_PRESCRIPTIONS +
                " WHERE " + KEY_PATIENT_ID + " = ?" +
                " ORDER BY " + KEY_CREATED_DATE + " DESC";

        Cursor cursor = db.rawQuery(selectQuery, new String[]{String.valueOf(patientId)});

        if (cursor.moveToFirst()) {
            do {
                Prescription prescription = new Prescription();
                prescription.setId(cursor.getInt(cursor.getColumnIndexOrThrow(KEY_ID)));
                prescription.setAppointmentId(cursor.getInt(cursor.getColumnIndexOrThrow(KEY_APPOINTMENT_ID)));
                prescription.setPatientId(cursor.getInt(cursor.getColumnIndexOrThrow(KEY_PATIENT_ID)));
                prescription.setDoctorId(cursor.getInt(cursor.getColumnIndexOrThrow(KEY_DOCTOR_ID_PRESCRIPTION)));
                prescription.setAiAnalysis(cursor.getString(cursor.getColumnIndexOrThrow(KEY_AI_ANALYSIS)));
                prescription.setFinalDiagnosis(cursor.getString(cursor.getColumnIndexOrThrow(KEY_FINAL_DIAGNOSIS)));
                prescription.setFinalPrescription(cursor.getString(cursor.getColumnIndexOrThrow(KEY_FINAL_PRESCRIPTION)));
                prescription.setCreatedDate(cursor.getString(cursor.getColumnIndexOrThrow(KEY_CREATED_DATE)));

                prescriptions.add(prescription);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        Log.d(TAG, "Fetched " + prescriptions.size() + " prescriptions for patient: " + patientId);
        return prescriptions;
    }

    /**
     * 获取医生的所有诊断记录
     */
    public List<Prescription> getPrescriptionsByDoctor(int doctorId) {
        List<Prescription> prescriptions = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        String selectQuery = "SELECT * FROM " + TABLE_PRESCRIPTIONS +
                " WHERE " + KEY_DOCTOR_ID_PRESCRIPTION + " = ?" +
                " ORDER BY " + KEY_CREATED_DATE + " DESC";

        Cursor cursor = db.rawQuery(selectQuery, new String[]{String.valueOf(doctorId)});

        if (cursor.moveToFirst()) {
            do {
                Prescription prescription = new Prescription();
                prescription.setId(cursor.getInt(cursor.getColumnIndexOrThrow(KEY_ID)));
                prescription.setAppointmentId(cursor.getInt(cursor.getColumnIndexOrThrow(KEY_APPOINTMENT_ID)));
                prescription.setPatientId(cursor.getInt(cursor.getColumnIndexOrThrow(KEY_PATIENT_ID)));
                prescription.setDoctorId(cursor.getInt(cursor.getColumnIndexOrThrow(KEY_DOCTOR_ID_PRESCRIPTION)));
                prescription.setAiAnalysis(cursor.getString(cursor.getColumnIndexOrThrow(KEY_AI_ANALYSIS)));
                prescription.setFinalDiagnosis(cursor.getString(cursor.getColumnIndexOrThrow(KEY_FINAL_DIAGNOSIS)));
                prescription.setFinalPrescription(cursor.getString(cursor.getColumnIndexOrThrow(KEY_FINAL_PRESCRIPTION)));
                prescription.setCreatedDate(cursor.getString(cursor.getColumnIndexOrThrow(KEY_CREATED_DATE)));

                prescriptions.add(prescription);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        Log.d(TAG, "Fetched " + prescriptions.size() + " prescriptions for doctor: " + doctorId);
        return prescriptions;
    }

    /**
     * 通过ID获取预约详情
     */
    public Appointment getAppointmentById(int appointmentId) {
        SQLiteDatabase db = this.getReadableDatabase();
        Appointment appointment = null;

        String selectQuery = "SELECT * FROM " + TABLE_APPOINTMENTS +
                " WHERE " + KEY_ID + " = ?";

        Cursor cursor = db.rawQuery(selectQuery, new String[]{String.valueOf(appointmentId)});

        if (cursor.moveToFirst()) {
            appointment = new Appointment();
            appointment.setId(cursor.getInt(cursor.getColumnIndexOrThrow(KEY_ID)));
            appointment.setPatientId(cursor.getInt(cursor.getColumnIndexOrThrow(KEY_PATIENT_ID)));
            appointment.setDeptId(cursor.getInt(cursor.getColumnIndexOrThrow(KEY_DEPT_ID)));
            appointment.setPatientName(cursor.getString(cursor.getColumnIndexOrThrow(KEY_PATIENT_NAME)));
            appointment.setIdCard(cursor.getString(cursor.getColumnIndexOrThrow(KEY_ID_CARD)));
            appointment.setAppointTime(cursor.getString(cursor.getColumnIndexOrThrow(KEY_APPOINT_TIME)));
            appointment.setStatus(cursor.getString(cursor.getColumnIndexOrThrow(KEY_STATUS)));
        }

        cursor.close();
        db.close();
        return appointment;
    }

    // 在Appointment相关操作中添加
    public List<Appointment> getAppointmentsByPatient(int patientId) {
        List<Appointment> appointments = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        String selectQuery = "SELECT * FROM " + TABLE_APPOINTMENTS +
                " WHERE " + KEY_PATIENT_ID + " = ?" +
                " ORDER BY " + KEY_APPOINT_TIME + " DESC";

        Cursor cursor = db.rawQuery(selectQuery, new String[]{String.valueOf(patientId)});

        if (cursor.moveToFirst()) {
            do {
                Appointment appointment = new Appointment();
                appointment.setId(cursor.getInt(0));
                appointment.setPatientId(cursor.getInt(1));
                appointment.setDeptId(cursor.getInt(2));
                appointment.setPatientName(cursor.getString(3));
                appointment.setIdCard(cursor.getString(4));
                appointment.setAppointTime(cursor.getString(5));
                appointment.setStatus(cursor.getString(6));

                appointments.add(appointment);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        Log.d(TAG, "Fetched " + appointments.size() + " appointments for patient: " + patientId);
        return appointments;
    }

    public String getDepartmentNameById(int deptId) {
        SQLiteDatabase db = this.getReadableDatabase();
        String deptName = "未知科室";

        String selectQuery = "SELECT " + KEY_DEPT_NAME + " FROM " + TABLE_DEPARTMENTS +
                " WHERE " + KEY_ID + " = ?";

        Cursor cursor = db.rawQuery(selectQuery, new String[]{String.valueOf(deptId)});

        if (cursor.moveToFirst()) {
            deptName = cursor.getString(0);
        }

        cursor.close();
        return deptName;
    }
}
